package org.deschutter.dump;

import org.deschutter.Constants;
import org.deschutter.URLReader;
import org.deschutter.Queries;
import org.deschutter.exceptions.CurrenttickNotFoundException;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import java.net.MalformedURLException;
import java.sql.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.math.BigDecimal;

/**
 * User: Berten De Schutter
 * Date: 06-Jul-2009
 * Time: 13:29:07
 * <p/>
 * This Class Handels the planet dump
 */
public class PlanetDump implements Constants, Job {


    public static void loadFileinDB(String file, Connection conn) throws SQLException {

        Statement st = null;
        ResultSet rs = null;

        st = conn.createStatement();
        st.executeUpdate("DELETE FROM planets_add");

        rs = st.executeQuery("LOAD DATA LOCAL INFILE '" + file + "' into table planets_add fields optionally enclosed by '\"' ignore 7 lines");

        rs.close();
        st.close();
    }

    public static void calculateScoreRank(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();

        st.executeUpdate("DELETE FROM planet_score_rank_temp");
        st.executeQuery("SET @rank=0;");
        int a = st.executeUpdate("INSERT INTO planet_score_rank_temp" +
                " SELECT @rank:=@rank+1 AS rank,planetname,rulername" +
                " FROM planets_add" +
                " order by `score` desc;");


        st.close();
    }

    public static void calculateValueRank(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();

        st.executeUpdate("DELETE FROM planet_value_rank_temp");
        st.executeQuery("SET @rank=0;");
        int a = st.executeUpdate("INSERT INTO planet_value_rank_temp" +
                " SELECT @rank:=@rank+1 AS rank,planetname,rulername" +
                " FROM planets_add" +
                " order by `value` desc;");


        st.close();
    }

    public static void calculateSizeRank(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();

        st.executeUpdate("DELETE FROM planet_size_rank_temp");
        st.executeQuery("SET @rank=0;");
        int a = st.executeUpdate("INSERT INTO planet_size_rank_temp" +
                " SELECT @rank:=@rank+1 AS rank,planetname,rulername" +
                " FROM planets_add" +
                " order by `size` desc;");


        st.close();
    }

    public static void populateHistory(Connection conn, String tick) throws SQLException {

        Statement st = null;


        String query = "INSERT INTO planets_history" +
                " (SELECT " +
                "p.x," +
                "p.y," +
                "p.z," +
                "p.planetname," +
                "p.rulername," +
                "p.race," +
                "p.size," +
                "p.score" +
                ",p.value," +
                "p.xp," +
                tick + " - 1," +
                "sr.ID," +
                "vr.ID," +
                "sir.ID " +
                "FROM planets p " +
                "INNER JOIN planet_score_rank sr ON (sr.planetname=p.planetname) " +
                "INNER JOIN planet_size_rank sir ON (sir.planetname=p.planetname) " +
                "INNER JOIN planet_value_rank vr on (vr.planetname=p.planetname))";

        st = conn.createStatement();

        int a = st.executeUpdate(query);


        st.close();
    }

    public static void populateCurrentTables(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();
        st.executeUpdate("DELETE FROM planets");
        st.executeUpdate("INSERT INTO planets SELECT * from planets_add");

        st.executeUpdate("DELETE FROM planet_score_rank");
        st.executeUpdate("INSERT INTO planet_score_rank SELECT * from planet_score_rank_temp");

        st.executeUpdate("DELETE FROM planet_size_rank");
        st.executeUpdate("INSERT INTO planet_size_rank SELECT * from planet_size_rank_temp");

        st.executeUpdate("DELETE FROM planet_value_rank");
        st.executeUpdate("INSERT INTO planet_value_rank SELECT * from planet_value_rank_temp");


        st.close();
    }

    public static void updateCurrenttick(Connection conn, String tick) throws SQLException {
        Statement st = null;

        st = conn.createStatement();
        st.executeUpdate("update bot_system set `value`=" + tick + " where `key`='" + CURRENT_TICK_SYSTEM_FIELD + "'");

        st.close();
    }
 public static void runTickDump(String planet_listing_url) {
 
        double time = System.currentTimeMillis();
        Connection conn = null;


        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?" +
                    "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);

            // Step 1:  Get planet_listing
            URLReader.copy(planet_listing_url, LOCAL_PLANET_LISTING);

            // Step 2: Get current Tick ( from planet dump)
            String dump = URLReader.get(planet_listing_url);
            Pattern compiledRegex;
            Matcher regexMatcher;
            compiledRegex = Pattern.compile("Tick: (\\d+)\\s*");
            regexMatcher = compiledRegex.matcher(dump.replace(",", ""));
            regexMatcher.find();
            String tick = regexMatcher.group(1);

            // Step 3: Load planet listing into table
            PlanetDump.loadFileinDB(LOCAL_PLANET_LISTING, conn);

            // Step 4: Calulate ranks
            PlanetDump.calculateScoreRank(conn);
            PlanetDump.calculateValueRank(conn);
            PlanetDump.calculateSizeRank(conn);

            // Step 5: Poluate history tables
            PlanetDump.populateHistory(conn, tick);

            // Step 6: Populate planets tables from temp tables
            PlanetDump.populateCurrentTables(conn);
            time = System.currentTimeMillis() - time;
            System.out.println("Tick " + tick + ": Planet Listing Successfully dumped in " + time / 1000 + " seconds");

            updateCurrenttick(conn, tick);
            conn.close();
        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
 }
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        runTickDump(PLANET_LISTING_URL);
    }
}
